package com.adminpanel.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;


import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;

import com.adminpanel.modelbean.ProductBean;
import com.adminpanel.modelbean.ProductTypeBean;

import com.adminpanel.util.Util;
import com.mysql.jdbc.Blob;



public class ProductService {
	
	private JdbcTemplate jdbcTemplate;
	private DataSource dataSource;
	private LobHandler lobHandler;
	
	
	//private DataFieldMaxValueIncrementer incre;
	
	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public void setLobHandler(LobHandler lobHandler) {
		this.lobHandler = lobHandler;
	}

	public LobHandler getLobHandler() {
		return lobHandler;
	}

	public void addProduct(final ProductBean productBean){
		
		jdbcTemplate.update("insert into adminpanel_product values(?,?,?,?,?,?,?,?,?)",
				new PreparedStatementSetter() {

					//@SuppressWarnings("static-access")
					@Override
					public void setValues(PreparedStatement ps) {
						
						try {
							ps.setInt(1, 0);
							ps.setInt(2, productBean.getUser_id());
							ps.setString(3, productBean.getProduct_id());
							ps.setString(4, productBean.getProduct_name());
							ps.setString(5, productBean.getProduct_describe());
							ps.setString(6, productBean.getProduct_type());
							ps.setString(7, productBean.getProduct_vendor());
							ps.setString(8, new Util().getDateTime());
							ps.setInt(9, productBean.getIsDraft());
						} catch (SQLException e) {
							

							e.printStackTrace();
						}
					}

				});
	}
	
	
//	public void addProduct(final ProductBean productBean){
//		String sql = " INSERT INTO adminpanel_product(id,user_id,product_id,product_name,product_describe,product_type,product_vendor,create_time,isDraft)" + " VALUES(?,?,?,?,?,?,?,?,?)";
//		jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
//
//					@Override
//					protected void setValues(PreparedStatement ps,
//							LobCreator lobCreator) throws SQLException,
//							DataAccessException {
//						ps.setInt(1, 0);
//						ps.setInt(2, productBean.getUser_id());
//						ps.setString(3, productBean.getProduct_id());
//						ps.setString(4, productBean.getProduct_name());
//						//ps.setBlob(5, productBean.getProduct_describe());
//						lobCreator.setBlobAsBytes(ps, 5, productBean.getProduct_describe());
//						ps.setString(6, productBean.getProduct_type());
//						ps.setString(7, productBean.getProduct_vendor());
//						ps.setString(8, new Util().getDateTime());
//						ps.setInt(9, productBean.getIsDraft());
//						
//					}
//
//				});
//	}
//	
	
	public List<ProductBean> getProductByUserId(int user_id){
		
		String sql = "select * from adminpanel_product where user_id = ?  order by id desc";
		RowMapper<ProductBean> mapper = new RowMapper<ProductBean>(){

			@Override
			public ProductBean mapRow(ResultSet rs, int rowNum) throws SQLException {
				
				ProductBean productBean = new ProductBean();
				productBean.setId(rs.getInt("id"));
				productBean.setUser_id(rs.getInt("user_id"));
				productBean.setProduct_id(rs.getString("product_id"));
				productBean.setProduct_name(rs.getString("product_name"));
				productBean.setProduct_describe(rs.getString("product_describe"));
				productBean.setProduct_type(rs.getString("product_type"));
				productBean.setProduct_vendor(rs.getString("product_vendor"));
				productBean.setCreate_time(rs.getString("create_time"));
				//productBean.setIsDraft(rs.getInt("isDraft"));
				return productBean;
				
			}
			
		};

		return jdbcTemplate.query(sql, mapper, new Object[]{user_id});
		
		
	}
	
	public ProductBean getProductDetailById(int id){
		String sql = "select * from adminpanel_product where id =?";
		final ProductBean productBean = new ProductBean();
		final Object[] params = new Object[]{id};
		jdbcTemplate.query(sql, params,new RowCallbackHandler(){
    		public void processRow(ResultSet rs) throws SQLException{
    			productBean.setId(rs.getInt("id"));
    			productBean.setProduct_id(rs.getString("product_id"));
    			productBean.setProduct_name(rs.getString("product_name"));
    			productBean.setProduct_describe(rs.getString("product_describe"));
    			//productBean.setProduct_describe((Blob) rs.getBlob("product_describe"));
    			productBean.setProduct_type(rs.getString("product_type"));
    			productBean.setProduct_vendor(rs.getString("product_vendor"));
    		}
    	});
		return productBean;
		
	}
	
	public void updateProduct(String product_desc,String product_type,int id){
		String sql = "update adminpanel_product set product_describe = ?,product_type = ? where id = ?";
		jdbcTemplate.update(sql, new Object[] {product_desc,product_type, id});
	}
	
	public void delProduct(String delid){
		String sql = "delete from adminpanel_product where id in (?)";
		jdbcTemplate.update(sql, new Object[]{delid});
	}
	
	
	
	public List<ProductTypeBean> getProductType(){
		String sql = "select * from product_type";
		RowMapper<ProductTypeBean> mapper = new RowMapper<ProductTypeBean>(){

			@Override
			public ProductTypeBean mapRow(ResultSet rs, int rowNum) throws SQLException {
				
				ProductTypeBean pt = new ProductTypeBean();
				pt.setId(rs.getInt("id"));
				pt.setType(rs.getString("type"));
				
				return pt;
				
			}
			
		};
		return jdbcTemplate.query(sql, mapper);
		
	}
	
	public void addProductType(final ProductTypeBean productTypeBean){
		jdbcTemplate.update("insert into product_type values(?,?)",
				new PreparedStatementSetter() {

					//@SuppressWarnings("static-access")
					@Override
					public void setValues(PreparedStatement ps) {
						
						try {
							ps.setInt(1, 0);
							ps.setString(2, productTypeBean.getType());
							
						} catch (SQLException e) {
							

							e.printStackTrace();
						}
					}

				});
	}
	
	public int findExistProductID(String product_id){
		String sqlStr = "select count(*) from adminpanel_product where product_id=?";
		return jdbcTemplate.queryForInt(sqlStr,new Object[]{product_id});
	}
	
	public int findExistProductName(String product_name){
		String sqlStr = "select count(*) from adminpanel_product where product_name=?";
		return jdbcTemplate.queryForInt(sqlStr,new Object[]{product_name});
	}
 }
